package com.library.repository.impl;

import com.library.entity.Book;
import com.library.entity.Borrow;
import com.library.entity.Reader;
import com.library.repository.BorrowRepository;
import com.library.utils.JDBCTools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BorrowRepositoryImpl implements BorrowRepository {

    /**
     * 在数据库中存储借阅信息
     * @param bookId
     * @param readerId
     * @param borrowTime
     * @param returnTime
     * @param adminId
     * @param state
     */
    @Override
    public void Insert(Integer bookId, Integer readerId, String borrowTime, String returnTime, Integer adminId, Integer state) {
        Connection connection = JDBCTools.getConnection();
        String sql = "insert into borrow(bookid,readerid,borrowtime,returntime,state) values(?,?,?,?,0)";
        PreparedStatement statement = null;
        try{
            statement = connection.prepareStatement(sql);
            statement.setInt(1, bookId);
            statement.setInt(2, readerId);
            statement.setString(3, borrowTime);
            statement.setString(4,returnTime);
            statement.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection,statement,null);
        }
    }

    /**
     * 通过ReaderID查询本读者的所有借阅信息
     * @param id
     * @return
     */
    @Override
    public List<Borrow> findAllByReaderID(Integer id,Integer index,Integer limit) {
        Connection conn = JDBCTools.getConnection();
        //建立list集合，用来存放最终借阅信息
        ArrayList<Borrow> list = new ArrayList<>();
        //执行的sql语句
        String sql = "select " +
                "br.id,b.name,b.author,b.publish,br.borrowtime,br.returntime,r.name,r.tel,r.cardid,br.state " +
                "from borrow br,book b,reader r " +
                "where readerid = ? and b.id = br.bookid and r.id = br.readerid limit ?,?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = conn.prepareStatement(sql);
            //配置sql语句的参数
            statement.setInt(1,id);
            statement.setInt(2,index);
            statement.setInt(3,limit);
            resultSet = statement.executeQuery();
            Borrow borrow = null;
            //遍历结果集
            while (resultSet.next()) {
                //获取结果集
                int borrowId = resultSet.getInt(1);
                String bookName = resultSet.getString(2);
                String author = resultSet.getString(3);
                String publish = resultSet.getString(4);
                String borrowTime = resultSet.getString(5);
                String returnTime = resultSet.getString(6);
                String readerName = resultSet.getString(7);
                String tel = resultSet.getString(8);
                String cardId = resultSet.getString(9);
                Integer state = resultSet.getInt(10);
                //封装对象
                borrow = new Borrow(borrowId, new Book(bookName, author, publish),
                        new Reader(readerName, tel, cardId), borrowTime, returnTime,state);
                //将对象放入list集合
                list.add(borrow);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(conn,statement,resultSet);
        }
        return list;
    }

    /**
     * 返回对应读者信息的借阅信息总条目 用来计算预计要展示的页数
     * @param id
     * @return
     */
    @Override
    public int countByReaderId(Integer id) {
        //执行sql语句进行查询
        Connection connection = JDBCTools.getConnection();
        String sql = "select count(*) from borrow where borrow.readerid = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Integer count = 0;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            resultSet = statement.executeQuery();
            //将获取到的数据保存成对象形式加入到数据中
            while (resultSet.next()){
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            System.out.println("出问题了");
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, statement, resultSet);
        }
        return count;
    }

    /**
     * 通过state状态查找所有已借阅未审核的借阅信息
     * @param state
     * @return
     */
    @Override
    public List<Borrow> findAllByState(Integer state,Integer index,Integer limit) {
        Connection conn = JDBCTools.getConnection();
        Borrow borrow = null;
        ArrayList<Borrow> list = new ArrayList<>();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String sql = "select " +
                "br.id,b.name,b.author,b.publish,br.borrowtime,br.returntime,r.name,r.tel,r.cardid,br.state " +
                "from borrow br,book b,reader r " +
                "where br.state = ? and b.id = br.bookid and r.id = br.readerid limit ?,?";
        try {
            statement = conn.prepareStatement(sql);
            statement.setInt(1, state);
            statement.setInt(2, index);
            statement.setInt(3, limit);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                //获取结果集
                int borrowId = resultSet.getInt(1);
                String bookName = resultSet.getString(2);
                String author = resultSet.getString(3);
                String publish = resultSet.getString(4);
                String borrowTime = resultSet.getString(5);
                String returnTime = resultSet.getString(6);
                String readerName = resultSet.getString(7);
                String tel = resultSet.getString(8);
                String cardId = resultSet.getString(9);
                Integer sta = resultSet.getInt(10);
                //封装对象
                borrow = new Borrow(borrowId, new Book(bookName, author, publish),
                        new Reader(readerName, tel, cardId), borrowTime, returnTime,sta);
                //将对象放入list集合
                list.add(borrow);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(conn,statement,resultSet);
        }
        return list;
    }

    /**
     * 统计此state状态下的借阅信息的个数
     * @param state
     * @return
     */
    @Override
    public int countByState(Integer state) {
        //执行sql语句进行查询
        Connection connection = JDBCTools.getConnection();
        String sql = "select count(*) from borrow where borrow.state = ?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Integer count = 0;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1,state);
            resultSet = statement.executeQuery();
            //将获取到的数据保存成对象形式加入到数据中
            while (resultSet.next()){
                count = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            System.out.println("出问题了");
            e.printStackTrace();
        } finally {
            JDBCTools.release(connection, statement, resultSet);
        }
        return count;
    }

}
